---
title: "sp73341_external_client_lookup stored procedure documentation"
summary: "Analysis of the sp73341_external_client_lookup Oracle function including business rules and operations."
status: draft
owner: "SAIF Corporation"
last_reviewed: 2026-08-31
source_schema: "WCIS_DBA"
source_name: "sp73341_external_client_lookup"
source_type: "PROCEDURE"
retrieval_status: "found"
retrieval_command: "legends oracle query --name sp73341_external_client_lookup"
retrieval_date: "2026-08-31"
---

# sp73341_external_client_lookup

## Overview

| Field | Value |
| --- | --- |
| **Schema** | `WCIS_DBA` |
| **Type** | Standalone function |
| **Database** | Oracle |
| **Status** | ✅ `found` — retrieved via Legends CLI |
| **Query executed** | `legends oracle query --name sp73341_external_client_lookup` |
| **Calling libraries** | `SAIF.Business.View.Policy` |
| **Calling classes** | `clsTeamAssignment` |

## Purpose

`sp73341_external_client_lookup` is the Oracle replacement for `sp_external_client_lookup`, returning
the external contractor email address for a system user id and last name. Per an in-source comment
(valrey, 08/29/2013), the source was changed from `net_registrant` to `system_user_contractor`.

## Procedures / Methods

### sp73341_external_client_lookup

| Field | Value |
| --- | --- |
| **Returns** | `VARCHAR2` (function return value) |
| **Called by** | `clsTeamAssignment.GetExternalEmailAddress` (`clsTeamAssignment.vb:412-440`, SQL at `279`) |

**Description:** Sources external client identity from `system_user_contractor`, replacing the legacy
`net_registrant` source, and returns NULL rather than raising an error on a lookup miss (see rules
PR-BVP-015, PR-BVP-016 in [Business Rules](#business-rules) below).

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `in_mailid` | IN | `VARCHAR2(30)` | — | System user id |
| `in_last_name` | IN | `VARCHAR2(65)` | — | Last name |
| *(return)* | RETURN | `VARCHAR2(100)` | — | Email address or NULL |

## Business Rules

Rules extracted from this function's logic:

| Rule ID | Rule | Condition | Action |
| --- | --- | --- | --- |
| PR-BVP-015 | External client identity is sourced from `system_user_contractor`, replacing the legacy `net_registrant` source | always | Cursor matches on `UPPER(TRIM(last_name))` and `UPPER(TRIM(system_user_id))` |
| PR-BVP-016 | A lookup miss yields NULL rather than an error | cursor `%notfound` | Return NULL |

> These rules are documented in [business-rules.md](../business-rules.md).

## Operations

Operations and processes implemented in this function:

| Operation | Description | Trigger |
| --- | --- | --- |
| Look up external client email (Oracle) | Returns the external contractor email for a system user id + last name | `clsTeamAssignment.GetExternalEmailAddress` (`clsTeamAssignment.vb:412-440`, SQL at `279`) |

> These operations are also listed in [operations-processes.md](../operations-processes.md) with source reference back to this document.

## Source Code

<details>
<summary>PL/SQL function (specification and body)</summary>

```sql
-- Oracle replacement for sp_external_client_lookup.
-- Source changed from net_registrant to system_user_contractor.
-- valrey 08/29/2013
FUNCTION sp73341_external_client_lookup (in_mailid    IN VARCHAR2,
                                         in_last_name IN VARCHAR2)
   RETURN VARCHAR2
IS
   CURSOR c_user IS
      SELECT email_address
        FROM system_user_contractor
       WHERE UPPER (TRIM (last_name))      = UPPER (TRIM (in_last_name))
         AND UPPER (TRIM (system_user_id)) = UPPER (TRIM (in_mailid));
BEGIN
   OPEN c_user;
   FETCH c_user INTO v_email;
   IF c_user%NOTFOUND THEN
      v_email := NULL;
   END IF;
   CLOSE c_user;
   RETURN v_email;
END;
```

</details>

## Related Documentation
- [Procedure catalog](index.md)
- [Business rules](../business-rules.md)
- [Operations and processes](../operations-processes.md)
- [SQL catalog](../sql/index.md)

## Sources
- Oracle DBA_SOURCE: `WCIS_DBA.sp73341_external_client_lookup`
- Legends CLI query executed 2026-08-31: `legends oracle query --name sp73341_external_client_lookup`
- Calling code: `SAIF.Business.View.Policy/clsTeamAssignment.vb:279, 412-440`